﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

/// <summary>
/// Access数据库操作类
/// </summary>
public static class AccessHelper
{

    private static OleDbConnection connection;

    /// <summary>
    /// OleDbConnection
    /// </summary>
    private static OleDbConnection Connection
    {
        get
        {
            string connectionString = ConfigurationManager.ConnectionStrings["AccessConnString"].ToString() + HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["AccessFilePath"].ToString());
            if (connection == null)
            {
                connection = new OleDbConnection(connectionString);
                connection.Open();
            }
            else if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection = new OleDbConnection(connectionString);
                connection.Open();
            }
            else if (connection.State == System.Data.ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
            return connection;
        }
    }

    #region 执行增删改ExecuteNonQuery
    /// <summary>
    /// 执行增删改
    /// </summary>
    /// <param name="safeSql"></param>
    /// <returns></returns>
    public static int ExecuteCommand(string safeSql)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(safeSql, Connection))
            {
                return cmd.ExecuteNonQuery();
            }
        }
        catch (OleDbException ex)
        {
            return -1;
        }
    }

    /// <summary>
    /// 执行增删改
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public static int ExecuteCommand(string sql, OleDbParameter value)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
            {
                cmd.Parameters.Add(value);
                return cmd.ExecuteNonQuery();
            }
        }
        catch (OleDbException ex)
        {
            return -1;
        }
    }

    /// <summary>
    /// 执行增删改
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static int ExecuteCommand(string sql, params OleDbParameter[] values)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                return cmd.ExecuteNonQuery();
            }
        }
        catch (OleDbException ex)
        {
            return -1;
        }
    }
    #endregion

    #region 查询单个值ExecuteScalar
    /// <summary>
    /// 查询单个值
    /// </summary>
    /// <param name="safeSql"></param>
    /// <returns></returns>
    public static object GetScalar(string safeSql)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(safeSql, Connection))
            {
                return cmd.ExecuteScalar();
            }
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 查询单个值
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public static object GetScalar(string sql, OleDbParameter value)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
            {
                cmd.Parameters.Add(value);
                return cmd.ExecuteScalar();
            }
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 查询单个值
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static object GetScalar(string sql, params OleDbParameter[] values)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                return cmd.ExecuteScalar();
            }
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
    }
    #endregion

    #region 返回DataReader
    /// <summary>
    /// 返回DataReader
    /// </summary>
    /// <param name="safeSql"></param>
    /// <returns></returns>
    public static OleDbDataReader GetReader(string safeSql)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(safeSql, Connection))
            {
                OleDbDataReader reader = cmd.ExecuteReader();
                return reader;
            }
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 返回DataReader
    /// </summary>
    /// <param name="safeSql"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public static OleDbDataReader GetReader(string safeSql, OleDbParameter value)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(safeSql, Connection))
            {
                cmd.Parameters.Add(value);
                OleDbDataReader reader = cmd.ExecuteReader();
                return reader;
            }
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 返回DataReader
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values)
    {
        try
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                OleDbDataReader reader = cmd.ExecuteReader();
                return reader;
            }
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
    }

    #endregion

    #region 返回dataTable
    /// <summary>
    /// 返回dataTable
    /// </summary>
    /// <param name="safeSql"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string safeSql)
    {
        DataSet ds = new DataSet();
        OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(ds);
        return ds.Tables[0];
    }

    /// <summary>
    /// 返回dataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql, OleDbParameter value)
    {
        DataSet ds = new DataSet();
        OleDbCommand cmd = new OleDbCommand(sql, Connection);
        cmd.Parameters.Add(value);
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(ds);
        return ds.Tables[0];
    }

    /// <summary>
    /// 返回dataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql, params OleDbParameter[] values)
    {
        DataSet ds = new DataSet();
        OleDbCommand cmd = new OleDbCommand(sql, Connection);
        cmd.Parameters.AddRange(values);
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(ds);
        return ds.Tables[0];
    }
    #endregion
}